package com.cxm.report.dao;

import com.cxm.report.dao.provider.ChannelReportProvider;
import com.cxm.report.model.criteria.ReportFilter;
import com.cxm.report.model.data.ChannelReport;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * @author scl.BlueOcean
 * @version 1.0
 * @date 2018年07月05 14:51
 * @description 渠道分析报告Dao
 */
@Mapper
public interface ChannelReportDao {
    /**删除指定日期的报表数据*/
    @DeleteProvider(type = ChannelReportProvider.class,method = "delete")
    void delete(String date);

    /**获取渠道数据*/
    @InsertProvider(type = ChannelReportProvider.class,method = "getChannelInfo")
    int insertChannelInfo(String date);

    /**更新反馈次数及平均反馈时间*/
    @UpdateProvider(type = ChannelReportProvider.class,method = "updateBackInfo")
    void updateBackInfo(String date);
    /**更新查询成功次数及成功平均反馈时间*/
    @UpdateProvider(type = ChannelReportProvider.class,method = "updateSuccessInfo")
    void updateSuccessInfo(String date);
    /**更新失败数*/
    @UpdateProvider(type = ChannelReportProvider.class,method = "updateFailedNum")
    void updateFailedNum(String date);
    /**更新查询超时数*/
    @UpdateProvider(type = ChannelReportProvider.class,method = "updateOverTimeNum")
    void updateOverTimeNum(String date);
    /**更新查无记录数*/
    @UpdateProvider(type = ChannelReportProvider.class,method = "updateNoRecordNum")
    void updateNoRecordNum(String date);
    /**更新查询中个数数*/
    @UpdateProvider(type = ChannelReportProvider.class,method = "updateInquiryNum")
    void updateInquiryNum(String date);





    @Select(value = {"select count(1) from t_channel_report where report_date=#{end}"})
    int count(String end);
    @Select(value = {"select channelName, requestNum orderNum, backNum, avgBackTime, successNum, avgSuccessTime,failedNum, overTimeNum,inquiryNum,noRecordNum, report_date from t_channel_report where report_date<=#{end} and report_date>=#{start} order by report_date desc"})
    List<ChannelReport> selectReport(@Param("start") String start, @Param("end") String end);

    @Select(value = {"select channelName, sum(requestNum) orderNum, sum(backNum) backNum, sum(avgBackTime*backNum)/sum(backNum) avgBackTime," +
            "sum(successNum) successNum,sum(failedNum) failedNum, sum(overTimeNum) overTimeNum,sum(inquiryNum) inquiryNum,sum(noRecordNum) noRecordNum," +
            "sum(avgSuccessTime*successNum)/sum(successNum) avgSuccessTime ," +
            "CONCAT(date_sub(report_date,INTERVAL WEEKDAY(report_date) DAY) ,' To ', date_sub(report_date,INTERVAL WEEKDAY(report_date) - 6 DAY) )reportDate " +
            "from t_channel_report where report_date<=#{end} and report_date>=#{begin}" +
            " GROUP by channelName,reportDate  order by reportDate asc,requestNum desc"})
    List<ChannelReport> getReportGroupByWeek(ReportFilter filter);

    @Select(value = {"select sum(requestNum) orderNum,sum(backNum) backNum,sum(successNum) successNum,sum(successNum*avgSuccessTime) avgSuccessTime from t_channel_report where report_date<=#{end} and report_date>=#{start}"})
    ChannelReport selectTotal(@Param("start") String start, @Param("end") String end);

    @Select(value = {"select channelName, sum(requestNum) orderNum, sum(backNum) backNum, sum(avgBackTime*backNum)/sum(backNum) avgBackTime," +
            "sum(successNum) successNum,sum(failedNum) failedNum, sum(overTimeNum) overTimeNum,sum(inquiryNum) inquiryNum,sum(noRecordNum) noRecordNum," +
            "sum(avgSuccessTime*successNum)/sum(successNum) avgSuccessTime ," +
            "date_sub(report_date,INTERVAL WEEKDAY(report_date) DAY) firstDay,date_sub(report_date,INTERVAL WEEKDAY(report_date) - 6 DAY) lastDay " +
            "from t_channel_report where report_date<=#{end} and report_date>=#{begin}" +
            " GROUP by channelName  order by requestNum desc"})
    List<ChannelReport> selectReportsOfScope(ReportFilter filter);

    /**
     * 日报
     * @param filter
     * @return
     */
    @Select(value = {"select channelName,requestNum orderNum,backNum,avgBackTime, successNum, failedNum,overTimeNum, inquiryNum,  noRecordNum," +
            " avgSuccessTime ,report_date from t_channel_report where report_date<=#{end} and report_date>=#{begin}" +
            " GROUP by channelName,report_date  order by report_date desc,requestNum desc"})
    List<ChannelReport> selectReports(ReportFilter filter);

    /**
     * 月报
     * @param filter
     * @return
     */
    @Select(value = {"select channelName, sum(requestNum) orderNum, sum(backNum) backNum, sum(avgBackTime*backNum)/sum(backNum) avgBackTime," +
            "sum(successNum) successNum,sum(failedNum) failedNum, sum(overTimeNum) overTimeNum,sum(inquiryNum) inquiryNum,sum(noRecordNum) noRecordNum," +
            "sum(avgSuccessTime*successNum)/sum(successNum) avgSuccessTime ," +
            "DATE_FORMAT(report_date, '%Y-%m') reportDate " +
            "from t_channel_report where report_date<=#{end} and report_date>=#{begin}" +
            " GROUP by channelName,reportDate  order by reportDate asc,requestNum desc"})
    List<ChannelReport> selectReportsGroupMonth(ReportFilter filter);
}
